﻿CREATE TRIGGER tr_Verification_Insert_Pos
ON dbo.t_Verification
FOR INSERT,UPDATE
AS

--IF @@ROWCOUNT > 1 AND UPDATE(Pos) BEGIN	RAISERROR('Bulk Verification Item Inserts/Updates are not supported!',16,1)	RETURN END
IF NOT UPDATE(Pos) RETURN
DECLARE @Base sysname SET @Base = 'tr_Verification_'
DECLARE @LevelInsert int SET @LevelInsert = TRIGGER_NESTLEVEL(Object_ID(@Base+'Insert_Pos'))
DECLARE @LevelDelete int SET @LevelDelete = TRIGGER_NESTLEVEL(Object_ID(@Base+'Delete_Pos'))
IF (@LevelInsert > 1) OR (@LevelDelete > 0)	RETURN

DECLARE @Pos int,@PosPrev int,@ID int,@ParentID int
SELECT @Pos = i.Pos, @ID = i.ID, @ParentID = i.ParentID,@PosPrev = d.Pos FROM inserted i LEFT OUTER JOIN deleted d ON i.ID = d.ID

IF @Pos <= 0 BEGIN -- move @Pos from the end
	SELECT @Pos = ISNULL(MAX(Pos),0) + 1 + @Pos FROM t_Verification WHERE ParentID = @ParentID
	IF @Pos <= @PosPrev SET @Pos = @Pos - 1 -- being moved up
	UPDATE t_Verification SET Pos = @Pos WHERE ID = @ID
END
ELSE IF @Pos > @PosPrev-- it's being moved down
	UPDATE t_Verification SET Pos = @Pos+1 FROM t_Verification WHERE ID = @ID

UPDATE t_Verification SET Pos = Pos + 1 WHERE ParentID = @ParentID AND Pos >= @Pos AND ID <> @ID

EXEC sp__sver_RePaginate @ParentID

